import datetime
import json

import numpy as np

from sklearn import metrics

from sklearn.neural_network import MLPRegressor
from sklearn.preprocessing import MinMaxScaler
import pymysql


def bpnnpredict_line(lineID, lineName, date):
    lineID = lineID
    lineName = lineName
    date = date
    dayType = datetime.datetime.strptime(date, "%Y-%m-%d").weekday() + 1
    # stationName = Station.objects.filter(stationid=stationID).values()[0]["stationname"]
    m = datetime.timedelta(hours=7)
    # station_test_flow = list()
    # station_pre_flow = list()
    line_test_flow = list()
    line_pre_flow = list()
    while m <= datetime.timedelta(hours=23):
        # station_res = bpnn_station_data(m, stationID, date)
        line_res = bpnn_line_data(m, lineID, date)
        # station_test_flow.append(station_res[0])
        # station_pre_flow.append(station_res[1])
        line_test_flow.append(line_res[0])
        line_pre_flow.append(line_res[1])
        m = m + datetime.timedelta(minutes=5)
    # station_mse = metrics.mean_squared_error(station_test_flow, station_pre_flow)
    # station_rmse = np.sqrt(station_mse)
    # station_mae = metrics.mean_absolute_error(station_test_flow, station_pre_flow)
    line_mse = metrics.mean_squared_error(line_test_flow, line_pre_flow)
    line_rmse = np.sqrt(line_mse)
    line_mae = metrics.mean_absolute_error(line_test_flow, line_pre_flow)
    response = {"lineID": lineID, "lineName": lineName, "date": date,
                "testflow": line_test_flow, "preflow": line_pre_flow,
                "mse": int(line_mse), "rmse": int(line_rmse), "mae": int(line_mae)},

    return response


def bpnnpredict_station(stationID, stationName, date):
    stationID = stationID
    stationName = stationName
    date = date
    m = datetime.timedelta(hours=7)
    station_test_flow = list()
    station_pre_flow = list()

    while m <= datetime.timedelta(hours=23):
        station_res = bpnn_station_data(m, stationID, date)
        station_test_flow.append(station_res[0])
        station_pre_flow.append(station_res[1])
        m = m + datetime.timedelta(minutes=5)
    station_mse = metrics.mean_squared_error(station_test_flow, station_pre_flow)
    station_rmse = np.sqrt(station_mse)
    station_mae = metrics.mean_absolute_error(station_test_flow, station_pre_flow)

    response = {"stationID": stationID, "stationName": stationName, "date": date,
                "testflow": station_test_flow, "preflow": station_pre_flow,
                "mse": int(station_mse), "rmse": int(station_rmse), "mae": int(station_mae)}

    return response


def day_type(day):
    if 1 <= day <= 5:
        return 0
    else:
        return 1
    return 1


def bpnn_station_data(time, stationID, date):
    conn = pymysql.connect(host='localhost',  # 连接名称
                           user='root',  # 用户名
                           passwd='q19723011',  # 密码
                           port=3306,  # 端口，默认为3306
                           db='month6',  # 数据库
                           charset='utf8',  # 字符编码
                           )
    cur = conn.cursor()  # 生成游标对象
    dataProcess = []
    start_time = "".join(str(time - datetime.timedelta(minutes=30)).split(':')[:3])
    sql = "select * from station_time_flow where time >= %s and  time <= %s and stationID = %d" \
          % (start_time, "".join(str(time).split(':')[:3]), int(stationID))
    cur.execute(sql)
    datas = list(cur.fetchall())
    ob_dayType = day_type(datetime.datetime.strptime(date, "%Y-%m-%d").date().weekday() + 1)
    ob_data_list = list()
    for i in range(0, len(datas), 7):
        if datas[i][1] == datetime.datetime.strptime(date, "%Y-%m-%d").date():
            ob_data_list = [datas[i][3], datas[i + 1][3],
                            datas[i + 2][3], datas[i + 3][3], datas[i + 4][3],
                            datas[i + 5][3], datas[i + 6][3]]
        elif day_type(datas[i][1].weekday() + 1) == ob_dayType:
            data_list = [datas[i][3], datas[i + 1][3],
                         datas[i + 2][3], datas[i + 3][3], datas[i + 4][3],
                         datas[i + 5][3], datas[i + 6][3]]
            dataProcess.append(data_list)
    dataProcess.append(ob_data_list)
    conn.commit()
    cur.close()  # 关闭游标
    conn.close()  # 关闭连接
    return train(dataProcess)


def bpnn_line_data(time, lineID, date):
    conn = pymysql.connect(host='localhost',  # 连接名称
                           user='root',  # 用户名
                           passwd='q19723011',  # 密码
                           port=3306,  # 端口，默认为3306
                           db='month6',  # 数据库
                           charset='utf8',  # 字符编码
                           )
    cur = conn.cursor()  # 生成游标对象
    dataProcess = []
    start_time = "".join(str(time - datetime.timedelta(minutes=30)).split(':')[:3])
    sql = "select * from line_time_flow where time >= %s and  time <= %s and lineID = %d" \
          % (start_time, "".join(str(time).split(':')[:3]), int(lineID))
    cur.execute(sql)
    datas = list(cur.fetchall())
    ob_dayType = day_type(datetime.datetime.strptime(date, "%Y-%m-%d").date().weekday() + 1)
    ob_data_list = list()
    for i in range(0, len(datas), 7):
        if datas[i][1] == datetime.datetime.strptime(date, "%Y-%m-%d").date():
            ob_data_list = [datas[i][3], datas[i + 1][3],
                            datas[i + 2][3], datas[i + 3][3], datas[i + 4][3],
                            datas[i + 5][3], datas[i + 6][3]]
        elif day_type(datas[i][1].weekday() + 1) == ob_dayType:
            data_list = [datas[i][3], datas[i + 1][3],
                         datas[i + 2][3], datas[i + 3][3], datas[i + 4][3],
                         datas[i + 5][3], datas[i + 6][3]]
            dataProcess.append(data_list)
    dataProcess.append(ob_data_list)

    conn.commit()
    cur.close()  # 关闭游标
    conn.close()  # 关闭连接
    return train(dataProcess)


def train(datas):
    # 处理数据，划分训练集，测试集，归一化处理，独热编码处理
    datas = np.array(datas)
    data_case = datas[:, 0:6]  # 获取特征值
    data_label = datas[:, 6:7]  # 获取标签
    mm = MinMaxScaler()
    data_label_process = mm.fit_transform(data_label)  # 对数据归一化处理
    mm_case = MinMaxScaler()
    data_case_process = mm_case.fit_transform(data_case)  # 对数据归一化处理
    test_data_case = data_case_process[len(data_case_process) - 1:]
    test_data_label = data_label_process[len(data_label_process) - 1:]
    train_data_case = data_case_process[0:len(data_case_process) - 1]
    train_data_label = data_label_process[0:len(data_label_process) - 1]
    # 训练模型
    model = MLPRegressor(hidden_layer_sizes=(7, 8, 8), activation='tanh', solver='adam', max_iter=2000,
                         learning_rate='adaptive', learning_rate_init=0.02)  # BP神经网络回归模型
    model.fit(train_data_case, train_data_label.ravel())  # 训练模型
    pre_train = model.predict(train_data_case)  # 模型训练集预测
    pre_test = model.predict(test_data_case)  # 模型测试机预测
    pre = mm.inverse_transform(np.append(pre_train, pre_test).reshape(1, -1))[0]  # 反归一化
    return [float(data_label[-1][0]), float(int(pre[-1]))]


def line_json():
    conn = pymysql.connect(host='localhost',  # 连接名称
                           user='root',  # 用户名
                           passwd='q19723011',  # 密码
                           port=3306,  # 端口，默认为3306
                           db='month6',  # 数据库
                           charset='utf8',  # 字符编码
                           )
    cur = conn.cursor()  # 生成游标对象
    sql = """select *from lineflow"""
    cur.execute(sql)
    datas = list(cur.fetchall())
    lineIDs = np.unique(np.array(datas)[:, 0:1])
    conn.commit()
    cur.close()  # 关闭游标
    conn.close()  # 关闭连接
    lineList = list()
    print(len(lineIDs))
    for i in range(len(lineIDs)):
        lineID = lineIDs[i]
        lineName = str(lineID) + "号线"
        print(lineID, lineName)
        for day in range(1, 3):
            if day < 10:
                day_str = "0" + str(day)
            else:
                day_str = str(day)
            print(lineID, "2018-06-" + day_str)
            lineJson = bpnnpredict_line(lineID, lineName, "2018-06-" + day_str)
            lineList.append(lineJson)
    return lineList


def station_json():
    conn = pymysql.connect(host='localhost',  # 连接名称
                           user='root',  # 用户名
                           passwd='q19723011',  # 密码
                           port=3306,  # 端口，默认为3306
                           db='month6',  # 数据库
                           charset='utf8',  # 字符编码
                           )
    cur = conn.cursor()  # 生成游标对象
    sql = """select *from station"""
    cur.execute(sql)
    datas = list(cur.fetchall())
    stationIDs = np.unique(np.array(datas)[:, 0:1])
    stationNames = np.unique(np.array(datas)[:, 1:2])
    conn.commit()
    cur.close()  # 关闭游标
    conn.close()  # 关闭连接
    stationList = list()
    for i in range(len(stationIDs)):
        stationID = stationIDs[i]
        stationName = stationNames[i]
        print(stationID, stationName)
        for day in range(1, 2):
            if day < 10:
                day_str = "0" + str(day)
            else:
                day_str = str(day)
            print(stationID, "2018-06-" + day_str)
            stationJson = bpnnpredict_station(stationID, stationName, "2018-06-" + day_str)
            stationList.append(stationJson)
            print(stationJson)


if __name__ == '__main__':
    station_json()
    # line = line_json()
    # print("完成")
    # bpnnPreJson = {
    #     "line": line
    # }
    # with open('bpnnPre.json', mode='w', encoding='utf-8') as f:
    #     json.dump(line, f)
    #
    # with open('bpnnPre.json', mode='r', encoding='utf-8') as f:
    #     dicts = json.load(f)
    #     # 将多个字典从json文件中读出来
    #     for i in dicts:
    #         print(i)

